SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE sp_FindGuid
	@Guid UNIQUEIDENTIFIER = NULL
AS
BEGIN
	-- Looks for a guid in every uniqueidentifier column in every table in the database.
	-- Lists the tables it was found in.
	SET NOCOUNT ON;
	DECLARE @Columns TABLE
	(
		TableName SYSNAME,
		ColumnName SYSNAME,
		TableID INT,
		ColumnID INT
	);
	DECLARE @Output TABLE
	(
		TableName SYSNAME,
		[SQL] NVARCHAR(4000)
	);
	DECLARE @Info TABLE
	(
		Info NVARCHAR(1000)
	);
	DECLARE @TableCount INT;
	DECLARE @ColumnCount INT;
	DECLARE @TableLoop INT;
	DECLARE @ColumnLoop INT;
	DECLARE @SQL NVARCHAR(4000);
	DECLARE @TableName SYSNAME;
	DECLARE @ColumnName SYSNAME;	
	
	IF @Guid IS NULL
	BEGIN
		INSERT INTO @Info (Info)
		VALUES ('EXEC sp_FindGuid ''your-guid'''),
		('Searches all tables and all guid columns'),
		('for that guid. Lists the table name and'),
		('the sql that will display it. Example:'),
		('exec sp_FindGuid ''aa8fa2d2-98d7-4190-9d24-95e82f3ff6b9''');

		SELECT Info FROM @Info;
		RETURN;
	END
	-- Get the list of tables and columns that have guids.
	INSERT INTO @Columns (TableName, ColumnName, TableID, ColumnID)
	SELECT 
		T.name As TableName,
		C.name AS ColumnName,
		DENSE_RANK() OVER (ORDER BY T.name) AS TableID,
		RANK() OVER (PARTITION BY T.name ORDER BY C.name) AS ColumnID
	FROM sysobjects AS T
	INNER JOIN syscolumns AS C
	ON T.id = C.id
	INNER JOIN systypes AS CT
	ON C.xtype = CT.xtype
	WHERE T.xtype = 'U'
	and CT.name = 'uniqueidentifier'
	ORDER BY T.name;
	
	SELECT @TableCount = MAX(TableID) FROM @Columns;
	SET @TableLoop = 1;
	WHILE @TableLoop <= @TableCount
	BEGIN
		SELECT @ColumnCount = MAX(ColumnID) 
		FROM @Columns 
		WHERE TableID = @TableLoop;

		SET @ColumnLoop = 1;
		SET @SQL = N'';
		WHILE @ColumnLoop <= @ColumnCount
		BEGIN
			SELECT 
				@TableName = TableName,
				@ColumnName = ColumnName 
			FROM @Columns 
			WHERE TableID = @TableLoop AND ColumnID = @ColumnLoop;
			IF LEN(@SQL) > 0
			BEGIN
				SET @SQL = CONCAT(@SQL,N' OR ');
			END;
			SET @SQL = CONCAT(@SQL,N'[',@ColumnName,N']=@SearchGuid');
			SET @ColumnLoop = @ColumnLoop + 1;
		END;
		SET @SQL = CONCAT(N'SELECT @GuidCount = COUNT(*) FROM [',@TableName,N'] WHERE ',@SQL);		
		EXEC sp_executesql @SQL,N'@GuidCount INT OUT,@SearchGuid UNIQUEIDENTIFIER',@ColumnCount OUT,@Guid;

		IF @ColumnCount > 0
		BEGIN
			INSERT INTO @Output (TableName,[SQL]) VALUES 
			(
				CONCAT(@TableName,N' (',@ColumnCount,N')'),
				REPLACE(REPLACE(@SQL,'@SearchGuid',CONCAT(N'''',@Guid,N'''')),N'@GuidCount = COUNT(*)',N'*')
			);
		END
		SET @TableLoop = @TableLoop +  1;
	END;

	SELECT TableName,[SQL]
	FROM @Output;
END
GO
